package com.system.controller;

import com.system.entity.Grade;
import com.system.entity.Takecourse;
import com.system.service.takeCourseService;
import com.system.service.teachCourseService;
import com.system.util.ExportExcel;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RequestMapping("/")
@Controller
public class SystemDownload {
    @Autowired
    private takeCourseService takeCourseService;
    @Autowired
    private teachCourseService teachCourseService;
    @RequestMapping(value = "mould", method = RequestMethod.POST)
    @ResponseBody
    public void download2(@RequestBody Map<String , String> map, HttpServletRequest request,
                         HttpServletResponse response) throws IOException {
        //先向excel导入数据
        Integer course_id = Integer.valueOf(map.get("course_id"));
        List<Takecourse> list =teachCourseService.findByEveryStudent(course_id) ;
        ExportExcel exportExcel =new ExportExcel();
        //checkPay.apk为需要下载的文件
        //String filename = "checkPay.apk";   //我这里使用的是一个固定的文件，方法可以不用写filename参数
        //获取文件的绝对路径名称，apk为根目录下的一个文件夹，这个只能获取根目录文件夹的绝对路径
        String filename = "gradeform.xls";
        String path = request.getSession().getServletContext().getRealPath("gradeForm") + "\\" + filename;
        exportExcel.InExcel2(list,path);
//      String path = "gradeForm/gradeform.xlsx";
        System.out.println(path);
        //得到要下载的文件
        File file = new File(path);
        if (!file.exists()) {
            response.setContentType("text/html; charset=UTF-8");//注意text/html，和application/html
            response.getWriter().print("<html><body><script type='text/javascript'>alert('您要下载的资源已被删除！');</script></body></html>");
            response.getWriter().close();
            System.out.println("您要下载的资源已被删除！！");
            return;
        }
        //转码，免得文件名中文乱码
        filename = URLEncoder.encode(filename, "UTF-8");
        //设置文件下载头
        response.addHeader("Content-Disposition", "attachment;filename=" + "gradeform.xlsx");
        //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
        response.setContentType("multipart/form-data");
        // 读取要下载的文件，保存到文件输入流
        FileInputStream in = new FileInputStream(path);
        // 创建输出流
        OutputStream out = response.getOutputStream();
        // 创建缓冲区
        byte buffer[] = new byte[4096]; // 缓冲区的大小设置是个迷  我也没搞明白
        int len = 0;
        //循环将输入流中的内容读取到缓冲区当中
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);
        }
        //关闭文件输入流
        in.close();
        // 关闭输出流
        out.close();
    }
    @RequestMapping(value = "gradeform", method = RequestMethod.POST)
    @ResponseBody
    public void download(@RequestBody Map<String , String> map, HttpServletRequest request,
                         HttpServletResponse response) throws IOException {
        //先向excel导入数据
//        Long tea_id = Long.valueOf(map.get("tea_id"));
        Integer course_id = Integer.valueOf(map.get("course_id"));
        String semester = map.get("semester");
        List<Takecourse> list =teachCourseService.findByEveryStudent(course_id) ;
        ExportExcel exportExcel =new ExportExcel();
        //checkPay.apk为需要下载的文件
        //String filename = "checkPay.apk";   //我这里使用的是一个固定的文件，方法可以不用写filename参数
        //获取文件的绝对路径名称，apk为根目录下的一个文件夹，这个只能获取根目录文件夹的绝对路径
        String filename = "gradeform.xls";
        String path = request.getSession().getServletContext().getRealPath("gradeForm") + "\\" + filename;
        exportExcel.InExcel(list,path);
//      String path = "gradeForm/gradeform.xlsx";
        System.out.println(path);
        //得到要下载的文件
        File file = new File(path);
        if (!file.exists()) {
            response.setContentType("text/html; charset=UTF-8");//注意text/html，和application/html
            response.getWriter().print("<html><body><script type='text/javascript'>alert('您要下载的资源已被删除！');</script></body></html>");
            response.getWriter().close();
            System.out.println("您要下载的资源已被删除！！");
            return;
        }
        //转码，免得文件名中文乱码
        filename = URLEncoder.encode(filename, "UTF-8");
        //设置文件下载头
        response.addHeader("Content-Disposition", "attachment;filename=" + "gradeform.xlsx");
        //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
        response.setContentType("multipart/form-data");
        // 读取要下载的文件，保存到文件输入流
        FileInputStream in = new FileInputStream(path);
        // 创建输出流
        OutputStream out = response.getOutputStream();
        // 创建缓冲区
        byte buffer[] = new byte[4096]; // 缓冲区的大小设置是个迷  我也没搞明白
        int len = 0;
        //循环将输入流中的内容读取到缓冲区当中
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);
        }
        //关闭文件输入流
        in.close();
        // 关闭输出流
        out.close();
    }
    @RequestMapping("stugradedownload")
    @ResponseBody
    public void download3(@RequestBody Map<String , String> map, HttpServletRequest request,
                          HttpServletResponse response) throws IOException {
//        Integer course_id = Integer.valueOf(map.get("course_id"));
        String semester = map.get("semester");
        Long stu_id = Long.valueOf(map.get("user_id"));  //提取前端传过来的学生学号
//        if(!map.get("course_id").equals(""))course_id = Integer.valueOf(map.get("course_id"));
        List<Grade> list =takeCourseService.findBySemester(stu_id,semester);
        ExportExcel exportExcel =new ExportExcel();
        //checkPay.apk为需要下载的文件
        //String filename = "checkPay.apk";   //我这里使用的是一个固定的文件，方法可以不用写filename参数
        //获取文件的绝对路径名称，apk为根目录下的一个文件夹，这个只能获取根目录文件夹的绝对路径
        String filename = "studentgradeform.xls";
        String path = request.getSession().getServletContext().getRealPath("gradeForm") + "\\" + filename;
        exportExcel.InExcel1(list,path);
//      String path = "gradeForm/gradeform.xlsx";
        System.out.println(path);
        //得到要下载的文件
        File file = new File(path);
        if (!file.exists()) {
            response.setContentType("text/html; charset=UTF-8");//注意text/html，和application/html
            response.getWriter().print("<html><body><script type='text/javascript'>alert('您要下载的资源已被删除！');</script></body></html>");
            response.getWriter().close();
            System.out.println("您要下载的资源已被删除！！");
            return;
        }
        //转码，免得文件名中文乱码
        filename = URLEncoder.encode(filename, "UTF-8");
        //设置文件下载头
        response.addHeader("Content-Disposition", "attachment;filename=" + "gradeform.xlsx");
        //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
        response.setContentType("multipart/form-data");
        // 读取要下载的文件，保存到文件输入流
        FileInputStream in = new FileInputStream(path);
        // 创建输出流
        OutputStream out = response.getOutputStream();
        // 创建缓冲区
        byte buffer[] = new byte[4096]; // 缓冲区的大小设置是个迷  我也没搞明白
        int len = 0;
        //循环将输入流中的内容读取到缓冲区当中
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);
        }
        //关闭文件输入流
        in.close();
        // 关闭输出流
        out.close();
    }
    /**
     * 导入成绩单
     *
     * @param
     * @param
     * @return
     * @throws IOException
     */

    @RequestMapping("importgrade")
    @ResponseBody
    public void importUsers(@RequestParam(name = "file") MultipartFile file, @RequestParam(name="course_id") String course_id,
                            HttpServletRequest request)
            throws IOException {
//        boolean FLAG;//身份状态
//        List<StudentGrade> list = new ArrayList<StudentGrade>();
        XSSFWorkbook workbook = null;
        //把MultipartFile转化为File 第一种
//        CommonsMultipartFile cmf = (CommonsMultipartFile) file;
//        DiskFileItem dfi = (DiskFileItem) cmf.getFileItem();
//        File fo = dfi.getStoreLocation();
        //把MultipartFile转化为File 第二种
        //可以存在磁盘里的文件
//        File fo = new File(file.getOriginalFilename());
//        FileUtils.copyInputStreamToFile(file.getInputStream(),fo);
//        file.transferTo(fo);
//        InputStream input = null;
        //创建Excel，读取文件内容
        workbook = new XSSFWorkbook(file.getInputStream());

        //获取第一个工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        //获取sheet中第一行行号
        int firstRowNum = sheet.getFirstRowNum();
        //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();
        try {
            //循环插入数据
            for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
                XSSFRow row = sheet.getRow(i);
                Takecourse student = new Takecourse();
                XSSFCell username = row.getCell(0);//学员名称
                System.out.println(username.getStringCellValue());
                if (username != null) {
                    username.setCellType(Cell.CELL_TYPE_STRING);
                    Long stu_id = Long.valueOf(username.getStringCellValue());
                    student.setStu_id(stu_id);
                }
                XSSFCell usualPerformance = row.getCell(1);//联系方式
                if (usualPerformance != null) {
                    usualPerformance.setCellType(Cell.CELL_TYPE_STRING);
                    student.setUsualPerformance((Integer.valueOf(usualPerformance.getStringCellValue())));
                }
                XSSFCell experience = row.getCell(2);//职位
                if (experience != null) {
                    experience.setCellType(Cell.CELL_TYPE_STRING);
                    student.setExperience((Integer.valueOf(experience.getStringCellValue())));
                }
                XSSFCell finalPerformance = row.getCell(3);//身份
                if (finalPerformance != null) {
                    finalPerformance.setCellType(Cell.CELL_TYPE_STRING);
                    student.setFinalPerformance(Integer.valueOf(finalPerformance.getStringCellValue()));
                }
                XSSFCell totalPerformance = row.getCell(4);
                if (totalPerformance != null) {
                    totalPerformance.setCellType(Cell.CELL_TYPE_STRING);
                    student.setTotalPerformance(Integer.valueOf(totalPerformance.getStringCellValue()));
                }
                student.setCourseId(Integer.valueOf(course_id));
//                list.add(student);
                takeCourseService.update(student);
            }

            //usersMapper.insert(list);//往数据库插入数据
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            workbook.close();
        }


    }
}

